package com.tanger.common.excel;

import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.*;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;

/**
 * 导出Excel格式文件 依赖包：jxl-2.6.jar
 * @Author Hchenbin
 */
public class ExportExcel {
	
	
	/** 
	 * 读取流 
	 *  
	 * @param inStream 
	 * @return 字节数组 
	 * @throws Exception 
	 */  
	public static byte[] readStream(InputStream inStream) throws Exception {
		if(inStream == null) return new byte[0];
	    ByteArrayOutputStream outSteam = new ByteArrayOutputStream();  
	    byte[] buffer = new byte[1024];  
	    int len = -1;  
	    while ((len = inStream.read(buffer)) != -1) {  
	        outSteam.write(buffer, 0, len);  
	    }  
	    outSteam.close();  
	    inStream.close();  
	    return outSteam.toByteArray();  
	}
	
	/**
	 * 
	 * 生成Excel表格
	 * 
	 * @param excelTitles 文件内容标题
	 * @param title th字段名称
	 * @param datas 三维数组   datas[第几个shell][第几行][第几列] 均从0开始
	 * @param mergeDatas 三维数组，合并表格信息   mergeDatas[第几个shell][第几个合并指令][合并信息]
	 * @return
	 * @throws Exception
	 */
	public static InputStream export(String [] excelTitles, String[][] title, Object[][][] datas, Integer [][][] mergeDatas) throws Exception{
		ByteArrayInputStream in = new ByteArrayInputStream(dispose(excelTitles, title, datas, mergeDatas));
		in.close();
		return in;
	}


	public static byte[] dispose(String [] excelTitles, String[][] title, Object[][][] datas, Integer [][][] mergeDatas) throws Exception{

		//表格第一行样式
		WritableCellFormat _cellFormatTop = getHeaderCellStyleTop();
		//商品表格头部标题样式
		WritableCellFormat cellFormat = getHeaderCellStyle("tableTop");
		//表格商品信息单元格样式
		WritableCellFormat cellFormatBody = getBodyCellStyle();
		//底部样式设置
		WritableCellFormat cellFormatButtom = getHeaderCellStyle("Buttom");

		ByteArrayOutputStream os = new ByteArrayOutputStream();
		WritableWorkbook book = Workbook.createWorkbook(os);
		try{
			for(int k = 0; k < excelTitles.length; k++){
				WritableSheet sheet = book.createSheet(excelTitles[k], 0);
				disposeSheetl(excelTitles[k], title[k], datas[k], mergeDatas[k], _cellFormatTop, cellFormat, cellFormatBody, cellFormatButtom, sheet);
			}
		}catch (Exception e){
			throw e;
		}finally {
			// 写入数据并关闭文件
			book.write();
			book.close();
			os.close();
		}

		return os.toByteArray();
	}


	private static void disposeSheetl(String excelTitles, String[] title, Object[][] datas, Integer[][] mergeDatas, WritableCellFormat _cellFormatTop, WritableCellFormat cellFormat,
									  WritableCellFormat cellFormatBody, WritableCellFormat cellFormatButtom, WritableSheet sheet) throws Exception{
		//设置第二行高度
		sheet.setRowView(1, 600);
		//设置第一行数据新增
		Label labelTop = new Label(0, 0, excelTitles);
		//居中
		labelTop.setCellFormat(_cellFormatTop);
		sheet.addCell(labelTop);

		int td_length = title.length;

		//定义商品table数据
		Label label;

		//设置标题
		for (int i = 0; i < td_length; i++){
			label = new Label(i, 1, title[i], cellFormat);
			sheet.addCell(label);
			//设置宽度
			sheet.setColumnView(i, 20);
		}

		//填充内容
		for (int i = 0; i < datas.length; i++) {
			for (int j = 0; j < datas[i].length; j++) {
				Object _d = datas[i][j];
				Label labels = new Label(j, i + 2, _d == null? "" : _d.toString(), cellFormatBody);
				sheet.addCell(labels);
			}
		}

		cellFormatButtom.setWrap(true);

		//合并行
		sheet.mergeCells(0, 0, title.length-1, 0);

		if(mergeDatas != null && mergeDatas.length > 0){
			for(int k = 0; k < mergeDatas.length; k++){
				Integer [] mergeData = mergeDatas[k];
				if(mergeData == null || mergeData.length == 0 || mergeData.length != 4) continue;
				/**
				 mergeCells(a,b,c,d) 单元格合并函数
					 a 单元格的列号
					 b 单元格的行号
					 c 从单元格[a,b]起，向下合并到c列
					 d 从单元格[a,b]起，向下合并到d行
				 注：单元格的列号和行号都是从0开始计
				 */
				sheet.mergeCells(mergeData[0], mergeData[1], mergeData[2], mergeData[3]);
			}
		}

	}


	
    /**     
     * @discription 首行标题样式设置
     * @return  首行样式
     */
    private static WritableCellFormat getHeaderCellStyleTop(){  
          
        /* 
         * WritableFont.createFont("宋体")：设置字体为宋体 
         * 10：设置字体大小 
         * WritableFont.BOLD:设置字体加粗（BOLD：加粗     NO_BOLD：不加粗） 
         * false：设置非斜体 
         * UnderlineStyle.NO_UNDERLINE：没有下划线 
         */  
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"),  
                                             20,   
                                             WritableFont.BOLD,   
                                             false,  
                                             UnderlineStyle.NO_UNDERLINE);  
          
        WritableCellFormat headerFormat = new WritableCellFormat(NumberFormats.TEXT);  
        try {  
            //添加字体设置  
            headerFormat.setFont(font); 
            
            
            //设置单元格背景色：表头为黄色  #9CF
            headerFormat.setBackground(Colour.WHITE);  
            
            //整个表格线为粗线、黑色  
            headerFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            
            //表头内容水平居中显示  
            headerFormat.setAlignment(jxl.format.Alignment.CENTRE);     
            
        } catch (final WriteException e) { 
        	e.printStackTrace();
        }  
        return headerFormat;  
    }
	
      
    /**     
     * @discription 表头单元格样式的设定 
     * @param operType 类型
     * @return   表头样式
     */
    private static WritableCellFormat getHeaderCellStyle(final String operType){  
          
        /* 
         * WritableFont.createFont("宋体")：设置字体为宋体 
         * 10：设置字体大小 
         * WritableFont.BOLD:设置字体加粗（BOLD：加粗     NO_BOLD：不加粗） 
         * false：设置非斜体 
         * UnderlineStyle.NO_UNDERLINE：没有下划线 
         */  
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"),  
                                             10,   
                                             WritableFont.BOLD,   
                                             false,  
                                             UnderlineStyle.NO_UNDERLINE);  
          
        WritableCellFormat headerFormat = new WritableCellFormat(NumberFormats.TEXT);  
        try {  
            //添加字体设置  
            headerFormat.setFont(font); 
            
            //整个表格线为粗线、黑色  
            headerFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            
            if(operType.equals("tableTop")){
            	//设置单元格背景色：表头为黄色  #9CF
                headerFormat.setBackground(Colour.GREY_25_PERCENT); 
                
            	//表头内容水平居中显示  
            	headerFormat.setAlignment(jxl.format.Alignment.CENTRE);     
                //把垂直对齐方式指定为居中
            	headerFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            }else{
            	//设置单元格背景色：表头为黄色  #9CF
                headerFormat.setBackground(Colour.WHITE); 
                
            	headerFormat.setAlignment(jxl.format.Alignment.LEFT);   
            	//把垂直对齐方式指定为居中
            	headerFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            }
            
        } catch (final WriteException e) { 
        	e.printStackTrace();
        }  
        return headerFormat;  
    }  
    
    /**     
     * @discription 表头单元格样式的设定
     * @return  样式   
     */
    private static WritableCellFormat getBodyCellStyle(){  
          
        /* 
         * WritableFont.createFont("宋体")：设置字体为宋体 
         * 10：设置字体大小 
         * WritableFont.NO_BOLD:设置字体非加粗（BOLD：加粗     NO_BOLD：不加粗） 
         * false：设置非斜体 
         * UnderlineStyle.NO_UNDERLINE：没有下划线 
         */  
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"),  
                                             10,   
                                             WritableFont.NO_BOLD,   
                                             false,  
                                             UnderlineStyle.NO_UNDERLINE);

		/*jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");
        WritableCellFormat bodyFormat = new WritableCellFormat(font, nf);*/

		WritableCellFormat bodyFormat = new WritableCellFormat(font);
        try {  
            //设置单元格背景色：表体为白色  
            bodyFormat.setBackground(Colour.WHITE);  
            
            //整个表格线为细线、黑色  
            bodyFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);  
            
            //表头内容水平居中显示  
            bodyFormat.setAlignment(jxl.format.Alignment.CENTRE);     
            //把垂直对齐方式指定为居中
            bodyFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            // 自动换行
            bodyFormat.setWrap(true);

        } catch (final WriteException e) {
        	e.printStackTrace();
        }  
        return bodyFormat;  
    }  
    
}
